"""
Case Type   : fullsql
Case Name   : 校验full sql关键字段不为负数
Create At   : 2022/01/27
Owner       : zhao-pengcheng-g
Description :
    1.创建表,插入数据
    2.查询last_updated字段在dbe_perf.statement中
    3.对表执行insert操作
    4.查询last_updated字段的值是否更新
    5.环境清理
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.成功
History     :
"""

import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0007 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 'table_fullsql_0007'

    def test_fullsql(self):
        text = '---step1:创建表,插入数据,expect:成功---'
        self.logger.info(text)
        create_cmd = f'''select reset_unique_sql('global', 'all', 1);
            set current_schema to public;
            create table {self.table_name}(id int, name char(10));
            insert into {self.table_name} values(1, 'name');'''
        create_msg = self.sh_primy.execut_db_sql(create_cmd)
        self.logger.info(create_msg)
        self.assertTrue('reset_unique_sql' in create_msg.splitlines()[0]
                        and 't' in create_msg.splitlines()[2]
                        and '(1 row)' in create_msg.splitlines()[3],
                        '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.CREATE_TABLE_SUCCESS and
                      self.constant.INSERT_SUCCESS_MSG,
                      create_msg, '执行失败:' + text)

        text = '---step2:查询last_updated字段在dbe_perf.statement中,' \
               'expect:成功---'
        self.logger.info(text)
        select_cmd = f'''select last_updated from dbe_perf.statement 
            where query like 'insert into {self.table_name} values%'
            order by last_updated desc;'''
        select_msg = self.sh_primy.execut_db_sql(select_cmd, dbname='postgres')
        self.logger.info(select_msg)
        last_updated_res = select_msg.splitlines()[2].strip()
        self.assertIn('last_updated', select_msg, '执行失败:' + text)

        text = '---step3:对表执行insert操作,expect:成功---'
        insert_cmd = f"insert into {self.table_name} values(2, 'name');"
        insert_msg = self.sh_primy.execut_db_sql(insert_cmd)
        self.logger.info(insert_msg)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      insert_msg, '执行失败:' + text)

        text = '---step4:查询last_updated字段,expect:成功---'
        self.logger.info(text)
        select_cmd = f'''select last_updated > '{last_updated_res}' \
            from dbe_perf.statement 
            where query like 'insert into {self.table_name} values%'
                and last_updated > '{last_updated_res}';'''
        select_msg = self.sh_primy.execut_db_sql(select_cmd, dbname='postgres')
        self.logger.info(select_msg)
        self.common.equal_sql_mdg(select_msg, '?column?',
                                  't', '(1 row)', flag='1')

    def tearDown(self):
        text = '---step5: 环境清理,expect:成功:---'
        self.logger.info(text)
        drop_cmd = f'drop table {self.table_name};'
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_msg, '执行失败:' + text)
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0007 finish---')
